﻿Public Class VolumesForm

    Dim PeriodCode As Integer
    Dim RID As String
    Dim UPIN As Integer
    Dim URN As String
    Dim Establishment_Number As String
    Dim CurrentLACode As String
    Dim CurrentRegion As String
    Dim ChangesMade As Boolean
    Dim InfoChangesMade As Boolean
    Dim CanLABoxIndexChangeRun As Boolean
    Dim CanVersionBoxIndexChangeRun As Boolean
    Dim YearLabel As String


    Public Sub LoadVersionBox()
        Dim sql As String

        Job("Loading Versions...")

        CanVersionBoxIndexChangeRun = False
        sql = "Select 'Live' as Version from global_settings_upgrade " & _
              "union " & _
              "select distinct cast(Version as char(4)) from dbo.Volumes_Data_Backup " & _
              "where RID = '" & RID & "' and Period_ID = " & PeriodCode

        VersionBox.DataSource = LoadDataView(sql)
        VersionBox.DisplayMember = "Version"
        VersionBox.ValueMember = "Version"

        VersionBox.SelectedValue = "Live"
        CanVersionBoxIndexChangeRun = True

    End Sub

    Private Sub Locking()
        Dim sql As String
        Dim TheView As DataView
        Dim r As Integer
        Dim x As Integer
        Dim Region_Code As String
        Dim skip As Boolean

        skip = False

        Region_Code = GetRegionCode(CurrentLACode)

        'Load the LockArray
        sql = "SELECT * FROM Lock_Rules " & _
              "Where (Period_ID = 0 Or Period_ID = " & PeriodBox.SelectedValue & ") " & _
              "and (Region_Code = '<All>' or Region_Code = '" & Region_Code & "') " & _
              "and (LA_Code = '<All>' or LA_Code = '" & CurrentLACode & "') " & _
              "and (UPIN = 0 or UPIN = " & UPIN & ") " & _
              "and (coalesce(Start_Date,0) = 0 or coalesce(Start_Date,0) >= " & Fix(Now.Date.ToOADate - 2) & ") " & _
              "and (coalesce(End_Date,0) = 0 or coalesce(End_Date,0) <= " & Fix(Now.Date.ToOADate - 2) & ") " & _
              "and Status = 1"

        TheView = LoadDataView(sql)

        If TheView.Count = 0 Then

            Exit Sub
        End If

        'check to see if perhaps all need to be locked
        For r = 0 To TheView.Count - 1
            If TheView(r).Item("Field_Code") = 0 Then
                skip = True

                For x = 0 To VolumesDataGrid.RowCount - 1
                    VolumesDataGrid.Item(VolumesDataGrid.Columns("Locked").Index, x).Value = 1
                Next
            End If
        Next

        If skip = False Then

            For r = 0 To TheView.Count - 1
                For x = 0 To VolumesDataGrid.RowCount - 1
                    If VolumesDataGrid.Item(VolumesDataGrid.Columns("Field_Code").Index, x).Value = TheView(r).Item("Field_Code") Then
                        VolumesDataGrid.Item(VolumesDataGrid.Columns("Locked").Index, x).Value = 1
                    End If
                Next
            Next
        End If

        'Load the UnLockArray

        skip = False
        sql = "SELECT * FROM Lock_Rules " & _
              "Where (Period_ID = 0 Or Period_ID = " & PeriodBox.SelectedValue & ") " & _
              "and (Region_Code = '<All>' or Region_Code = '" & Region_Code & "') " & _
              "and (LA_Code = '<All>' or LA_Code = '" & CurrentLACode & "') " & _
              "and (UPIN = 0 or UPIN = " & UPIN & ") " & _
              "and (coalesce(Start_Date,0) = 0 or coalesce(Start_Date,0) >= " & Now.Date & ") " & _
              "and (coalesce(End_Date,0) = 0 or coalesce(End_Date,0) <= " & Now.Date & ") " & _
              "and Status = 0"

        TheView = LoadDataView(sql)

        'check to see if perhaps all need to be unlocked
        For r = 0 To TheView.Count - 1
            If TheView(r).Item("Field_Code") = 0 Then

                skip = True
                For x = 0 To VolumesDataGrid.RowCount - 1
                    VolumesDataGrid.Item(VolumesDataGrid.Columns("Locked").Index, x).Value = 0
                Next
            End If
        Next

        If skip = False Then

            For r = 0 To TheView.Count - 1
                For x = 0 To VolumesDataGrid.RowCount - 1
                    If VolumesDataGrid.Item(VolumesDataGrid.Columns("Field_Code").Index, x).Value = TheView(r).Item("Field_Code") Then
                        VolumesDataGrid.Item(VolumesDataGrid.Columns("Locked").Index, x).Value = 0
                    End If
                Next
            Next
        End If

        ChangesMade = False
    End Sub

    Private Sub SetButtons()
        If CurrentUser.Level > 1 Then
            If VersionBox.SelectedValue.ToString = "Live" Then
                SaveButton.Enabled = True
                ImportButton.Enabled = True
                ExportButton.Enabled = True
                ReportButton.Enabled = True
                LiveButton.Enabled = False
            Else
                SaveButton.Enabled = False
                ImportButton.Enabled = False
                ExportButton.Enabled = False
                ReportButton.Enabled = False
                LiveButton.Enabled = True
            End If
        End If

        'Funding Grid

        If CurrentUser.Level = 5 Then
            Try
                If FundingGrid.CurrentRow.Index > -1 Then
                    If FundingGrid.Item(3, FundingGrid.CurrentRow.Index).Value.ToString = "1" Then
                        ChangeReqBut.Enabled = False
                    Else
                        If FundingGrid.Item(3, FundingGrid.CurrentRow.Index).Value.ToString = "0" Then
                            ChangeReqBut.Enabled = False
                        Else
                            ChangeReqBut.Enabled = True
                        End If
                    End If
                End If
            Catch ex As Exception
                'just do nothing
            End Try

        Else
            ChangeReqBut.Enabled = False
        End If

    End Sub

    Private Sub LoadNewRID(ByVal theUPIN As Integer, ByVal LACode As String)
        Dim RIDData As DataView
        Dim sql As String


        sql = "Select RID from Relationships where UPIN = " & theUPIN.ToString & " and LA_Code = '" & LACode & "'"

        RIDData = LoadDataView(sql)

        RID = RIDData(0).Item("RID").ToString

        RIDData = Nothing

        '   LoadURNandEstab(theUPIN)
    End Sub

    Private Sub LoadURNandEstab(ByVal theUPIN As Integer)
        Dim TheData As DataView
        Dim sql As String

        sql = "Select URN, Establishment_Number from Providers where UPIN = " & theUPIN.ToString

        TheData = LoadDataView(sql)

        URN = TheData(0).Item("URN").ToString
        Establishment_Number = TheData(0).Item("Establishment_Number").ToString


        TheData = Nothing
    End Sub

    Private Sub LoadPeriodBox()
        Dim sql As String

        Job("Loading Periods...")

        sql = "select * from Periods"

        PeriodBox.DataSource = LoadDataView(sql)
        PeriodBox.ValueMember = "Period_ID"
        PeriodBox.DisplayMember = "Period_Name"

        PeriodBox.SelectedValue = CurrentPeriodID
        PeriodCode = CurrentPeriodID

    End Sub

    Private Sub LoadHistoryGrid()
        Dim sql As String
        Dim c As Integer

        Job("Loading History..")

        sql = "select Status, TheDate as 'Date', [User_Name] as 'User', b.Budget_ID, b.ID, b.Description,  coalesce(a.Allocation,0) as Allocation, " & _
            "case when (coalesce(a.Rate,0) * coalesce(a.Provider_Factor,0)) = 0 then 0 else " & _
            "coalesce(a.Allocation,0) / (coalesce(a.Rate,0) * coalesce(a.Provider_Factor,0)) end as SLN,  " & _
            "coalesce(a.rate,0) as Rate, coalesce(a.Provider_Factor,0) as [Provider Factor], coalesce(Learner_Numbers,0) as Learners, " & _
            "case when (coalesce(a.Rate,0) * coalesce(a.Provider_Factor,0)) = 0 then 0 " & _
            "     when coalesce(Learner_Numbers,0) = 0 then 0 else " & _
            "(coalesce(a.Allocation,0) / (coalesce(a.Rate,0) * coalesce(a.Provider_Factor,0))) / Learner_Numbers end as 'SLN Ratio', " & _
            "Notes " & _
            "from  " & _
            "(Select * from Allocations Where Period_ID = " & PeriodBox.SelectedValue & " and RID = '" & RID & "') a   " & _
            "right outer join  (select Budget_ID as Budget_ID, Allocation_ID as ID, Allocation_Description as Description, Field_Code from Allocation_Lines) b on a.Allocation_ID = b.ID "

        If FundingGrid.Item(FundingGrid.Columns("Type").Index, FundingGrid.CurrentRow.Index).Value = 1 Then
            sql = sql & " where Budget_ID = '" & FundingGrid.Item(FundingGrid.Columns("Budget_ID").Index, FundingGrid.CurrentRow.Index).Value & "'"
        Else
            If FundingGrid.Item(FundingGrid.Columns("Type").Index, FundingGrid.CurrentRow.Index).Value = 0 Then
            Else
                sql = sql & " where ID = '" & FundingGrid.Item(FundingGrid.Columns("ID").Index, FundingGrid.CurrentRow.Index).Value & "'"
            End If
        End If

        HistoryGrid.DataSource = LoadDataView(sql)

        For c = 0 To HistoryGrid.Columns.Count - 1
            HistoryGrid.Columns(c).ReadOnly = True

        Next

        HistoryGrid.Columns("Budget_ID").Visible = False
        HistoryGrid.Columns("ID").Visible = False

        If FundingGrid.Item(FundingGrid.Columns("Type").Index, FundingGrid.CurrentRow.Index).Value < 2 Then
            HistoryGrid.Columns("Description").Visible = True
        Else
            HistoryGrid.Columns("Description").Visible = False
        End If
        Job("Ready")
    End Sub

    Private Sub LoadFundingGrid()
        Dim Sql As String
        Dim c As Integer

        Job("Loading Funding...")

        '       Sql = "Select Budget_ID, ID, Description, Type, cast(Allocation as decimal(14,2)) as Allocation, cast(SLN as decimal(5,2)) as SLN, Rate, [Provider Factor] " & _
        '       "from (select c.Budget_ID, c.Budget_ID as ID, b.Budget_Description as Description, '1' as Type, sum(Allocation) as Allocation, " & _
        '       "Null as SLN, null as Rate, Null as [Provider Factor] " & _
        '       "from (select b.Budget_ID, coalesce(a.Allocation,0) as Allocation " & _
        '       "from (Select * from Allocations " & _
        '       "Where Period_ID = " & PeriodBox.SelectedValue & " and " & _
        '       "RID = '" & RID & "' and " & _
        '       "Status = 'Approved') a " & _
        '       "right outer join " & _
        '       "(select Budget_ID as Budget_ID, Allocation_ID as ID, Allocation_Description as Description, '2' as Type from Allocation_Lines) b " & _
        '       "on a.Allocation_ID = b.ID) c, " & _
        '"Budgets b " & _
        '"where b.Budget_ID = c.Budget_ID " & _
        '"group by c.Budget_ID, b.Budget_Description " & _
        '"union all " & _
        '"select b.Budget_ID, b.ID, b.Description, b.Type, " & _
        '"coalesce(a.Allocation,0) as Allocation,  " & _
        '"case  " & _
        '"when (coalesce(a.Rate,0) * coalesce(a.Provider_Factor,0)) = 0 then 0 else " & _
        '"coalesce(a.Allocation,0) / (coalesce(a.Rate,0) * coalesce(a.Provider_Factor,0)) " & _
        '"end as SLN, " & _
        '"coalesce(a.rate,0) as Rate, " & _
        '"coalesce(a.Provider_Factor,0) as [Provider Factor] " & _
        '" from " & _
        '"(Select * from Allocations " & _
        '"Where Period_ID = " & PeriodBox.SelectedValue & " and " & _
        '"RID = '" & RID & "' and " & _
        '"Status = 'Approved') a  " & _
        '"right outer join  " & _
        '"(select Budget_ID as Budget_ID, Allocation_ID as ID, Allocation_Description as Description, '2' as Type from Allocation_Lines) b " & _
        '"on a.Allocation_ID = b.ID) Badboy " & _
        '"order by Budget_ID, Type "

        Sql = "Select Budget_ID, ID, Description, Type, Allocation, " & _
        "SLN, Rate, [Provider Factor], Learners, SLNRatio as 'SLN Ratio' " & _
        "from  " & _
        "( " & _
        " " & _
        "select 'Total' as Budget_ID, 'Total' as ID, 'Total Approved Allocation' as Description, '0' as Type,  " & _
        "sum(Allocation) as Allocation, Null as SLN, null as Rate, Null as [Provider Factor], 0 as Learners, " & _
        "0 as SLNRatio  " & _
        "from  " & _
        "(select b.Budget_ID, coalesce(a.Allocation,0) as Allocation, coalesce(Learner_Numbers,0) as Learners " & _
        "from (Select * from Allocations Where Period_ID = " & PeriodBox.SelectedValue & " and RID = '" & RID & "'  " & _
        "and Status = 'Approved') a  " & _
        "right outer join (select Budget_ID as Budget_ID, Allocation_ID as ID, Allocation_Description as Description,  " & _
        "'2' as Type, Field_Code from Allocation_Lines) b on a.Allocation_ID = b.ID " & _
        ") c,  " & _
        "Budgets b where b.Budget_ID = c.Budget_ID  " & _
        " " & _
        "union all " & _
        " " & _
        "select c.Budget_ID, c.Budget_ID as ID, b.Budget_Description as Description, '1' as Type,  " & _
        "sum(Allocation) as Allocation, Null as SLN, null as Rate, Null as [Provider Factor], 0 as Learners, " & _
        "0 as SLNRatio  " & _
        "from  " & _
        "(select b.Budget_ID, coalesce(a.Allocation,0) as Allocation, coalesce(Learner_Numbers,0) as Learners " & _
        "from (Select * from Allocations Where Period_ID = " & PeriodBox.SelectedValue & " and RID = '" & RID & "'  " & _
        "and Status = 'Approved') a  " & _
        "right outer join (select Budget_ID as Budget_ID, Allocation_ID as ID, Allocation_Description as Description,  " & _
        "'2' as Type, Field_Code from Allocation_Lines) b on a.Allocation_ID = b.ID " & _
        ") c,  " & _
        "Budgets b where b.Budget_ID = c.Budget_ID group by c.Budget_ID, b.Budget_Description  " & _
        " " & _
        "union all " & _
        " " & _
        "select b.Budget_ID, b.ID, b.Description, b.Type, coalesce(a.Allocation,0) as Allocation,  " & _
        "case when (coalesce(a.Rate,0) * coalesce(a.Provider_Factor,0)) = 0 then 0 else  " & _
        "coalesce(a.Allocation,0) / (coalesce(a.Rate,0) * coalesce(a.Provider_Factor,0)) end as SLN,  " & _
        "coalesce(a.rate,0) as Rate, coalesce(a.Provider_Factor,0) as [Provider Factor], coalesce(Learner_Numbers,0) as Learners, " & _
        "case when (coalesce(a.Rate,0) * coalesce(a.Provider_Factor,0)) = 0 then 0 " & _
        "     when coalesce(Learner_Numbers,0) = 0 then 0 else " & _
        "(coalesce(a.Allocation,0) / (coalesce(a.Rate,0) * coalesce(a.Provider_Factor,0))) / Learner_Numbers end as SLNRatio " & _
        "from  " & _
        "(Select * from Allocations Where Period_ID = " & PeriodBox.SelectedValue & " and RID = '" & RID & "' and Status = 'Approved' " & _
        ") a   " & _
        "right outer join  (select Budget_ID as Budget_ID, Allocation_ID as ID, Allocation_Description as Description,  " & _
        "'2' as Type, Field_Code from Allocation_Lines where Allocation_ID in (select distinct allocation_ID from dbo.Allocations where Status = 'Approved' and Period_ID = " & PeriodBox.SelectedValue & ")) b on a.Allocation_ID = b.ID " & _
        ") Badboy  " & _
        "order by Budget_ID, Type, ID "

        FundingGrid.DataSource = LoadDataView(Sql)

        FundingGrid.Columns(0).Visible = False
        FundingGrid.Columns(1).Visible = False
        FundingGrid.Columns(3).Visible = False

        For c = 0 To FundingGrid.Columns.Count - 1
            FundingGrid.Columns(c).ReadOnly = True
        Next

        'Stop user from reordering the grid
        Dim i As Integer

        For i = 0 To FundingGrid.Columns.Count - 1

            FundingGrid.Columns.Item(i).SortMode = DataGridViewColumnSortMode.NotSortable

        Next
        FundingGrid.Refresh()
    End Sub

    Private Sub VolumesForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        LoadPeriodBox()


        LoadInfoGrid()


        LoadBrowsers()
        ChangeReqBut.Enabled = False

        'set user
        If CurrentUser.Level < 2 Then
            'Only from level 2 can update volumes
            SaveButton.Enabled = False
            ImportButton.Enabled = False
            LiveButton.Enabled = False
            ExportButton.Enabled = False

        End If

        If CurrentUser.Level = 5 Then
            'admin user
            ChangeReqBut.Enabled = True

        End If

        EdubaseBrowser.ScriptErrorsSuppressed = True
        DCSFBrowser.ScriptErrorsSuppressed = True
        OfstedBrowser.ScriptErrorsSuppressed = True



    End Sub

    Private Sub LoadBrowsers()
        Job("Loading additional information...")

        Dim NoDataString As String
        Const DCSFLink = "http://www.dcsf.gov.uk/cgi-bin/performancetables/school_09.pl"
        Const EdubaseLink = "http://www.edubase.gov.uk/establishment/summary.xhtml"
        Const OfstedLink = "http://www.ofsted.gov.uk/oxedu_providers/full/(urn)/"

        NoDataString = "<html><body><h1><b>No data is available at this time.  The data should be available soon.</b></h1></body></html>"

        If URN = "" Then
            OfstedBrowser.DocumentText = NoDataString
            EdubaseBrowser.DocumentText = NoDataString
        Else
            OfstedBrowser.Navigate(OfstedLink & URN)
            EdubaseBrowser.Navigate(EdubaseLink & "?urn=" & URN)
        End If

        If Establishment_Number = "" Then
            DCSFBrowser.DocumentText = NoDataString

        Else
            DCSFBrowser.Navigate(DCSFLink & "?No=" & CurrentLACode & Establishment_Number & "&Mode=Z&Type=SC&Begin=s&Phase=1&Year=09&Base=b&Num=" & CurrentLACode)

        End If

    End Sub

    Private Sub VolumesDataGrid_CellFormatting(ByVal sender As Object, _
    ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles VolumesDataGrid.CellFormatting


        On Error GoTo ErrorCatch


        e.CellStyle.Font = New Font("Arial", 8.25, FontStyle.Regular)

        'Hide the columns that the user does not need to see
        If VolumesDataGrid.Columns(e.ColumnIndex).Name.Equals("Field_Code") Then
            VolumesDataGrid.Columns(e.ColumnIndex).Visible = False
        End If

        If VolumesDataGrid.Columns(e.ColumnIndex).Name.Equals("Order_Field") Then
            VolumesDataGrid.Columns(e.ColumnIndex).Visible = False
        End If

        If VolumesDataGrid.Columns(e.ColumnIndex).Name.Equals("Category_Code") Then
            VolumesDataGrid.Columns(e.ColumnIndex).Visible = False
        End If

        If VolumesDataGrid.Columns(e.ColumnIndex).Name.Equals("Has_Achievement") Then
            VolumesDataGrid.Columns(e.ColumnIndex).Visible = False
        End If

        If VolumesDataGrid.Columns(e.ColumnIndex).Name.Equals("Is_Total_Line") Then
            VolumesDataGrid.Columns(e.ColumnIndex).Visible = False
        End If

        If VolumesDataGrid.Columns(e.ColumnIndex).Name.Equals("Is_Header_Line") Then
            VolumesDataGrid.Columns(e.ColumnIndex).Visible = False
        End If

        If VolumesDataGrid.Columns(e.ColumnIndex).Name.Equals("Is_Auto_Total_Line") Then
            VolumesDataGrid.Columns(e.ColumnIndex).Visible = False
        End If

        If VolumesDataGrid.Columns(e.ColumnIndex).Name.Equals("Locked") Then
            VolumesDataGrid.Columns(e.ColumnIndex).Visible = False
        End If

        If VolumesDataGrid.Columns(e.ColumnIndex).Name <> " " Then
            VolumesDataGrid.Item(e.ColumnIndex, e.RowIndex).ReadOnly = False
        Else
            VolumesDataGrid.Item(e.ColumnIndex, e.RowIndex).ReadOnly = True
        End If

        'set Header Rows
        If VolumesDataGrid.Item(VolumesDataGrid.Columns("Is_Header_Line").Index, e.RowIndex).Value = 1 And VolumesDataGrid.Item(VolumesDataGrid.Columns("Is_Total_Line").Index, e.RowIndex).Value = 0 Then
            e.CellStyle.BackColor = Color.LightGray
            e.CellStyle.Font = New Font("Arial", 8.25, FontStyle.Bold)
            VolumesDataGrid.Item(e.ColumnIndex, e.RowIndex).ReadOnly = True
        End If

        'set Total Header Line
        If VolumesDataGrid.Item(VolumesDataGrid.Columns("Is_Header_Line").Index, e.RowIndex).Value = 1 And VolumesDataGrid.Item(VolumesDataGrid.Columns("Is_Total_Line").Index, e.RowIndex).Value = 1 Then
            e.CellStyle.BackColor = Color.LightBlue
            e.CellStyle.Font = New Font("Arial", 8.25, FontStyle.Bold)

            If VolumesDataGrid.Columns(e.ColumnIndex).Name <> " " Then
                VolumesDataGrid.Item(e.ColumnIndex, e.RowIndex).ReadOnly = False
            Else
                VolumesDataGrid.Item(e.ColumnIndex, e.RowIndex).ReadOnly = True
            End If

        End If

        'set total lines
        If VolumesDataGrid.Item(VolumesDataGrid.Columns("Is_Header_Line").Index, e.RowIndex).Value = 0 And VolumesDataGrid.Item(VolumesDataGrid.Columns("Is_Total_Line").Index, e.RowIndex).Value = 1 Then
            e.CellStyle.Font = New Font("Arial", 8.25, FontStyle.Bold)

            If VolumesDataGrid.Columns(e.ColumnIndex).Name <> " " Then
                VolumesDataGrid.Item(e.ColumnIndex, e.RowIndex).ReadOnly = False
            Else
                VolumesDataGrid.Item(e.ColumnIndex, e.RowIndex).ReadOnly = True
            End If
        End If

        'Allignment of the input columns and formating of the numbers
        If VolumesDataGrid.Columns(e.ColumnIndex).Name = YearLabel & " Learners" Or VolumesDataGrid.Columns(e.ColumnIndex).Name = YearLabel & " Achieve" Then
            e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            VolumesDataGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "#,#"

        End If

        If VolumesDataGrid.Columns(e.ColumnIndex).Name = "2008/09 F05 Learners" Or VolumesDataGrid.Columns(e.ColumnIndex).Name = "2009/10 F01 Learners" Then
            e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            VolumesDataGrid.Item(e.ColumnIndex, e.RowIndex).ReadOnly = True
            VolumesDataGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "#,#"
        End If

        'Do locked cells
        If VolumesDataGrid.Item(VolumesDataGrid.Columns("Is_Header_Line").Index, e.RowIndex).Value = 1 And VolumesDataGrid.Item(VolumesDataGrid.Columns("Is_Total_Line").Index, e.RowIndex).Value = 0 Then
            VolumesDataGrid.Item(VolumesDataGrid.Columns(YearLabel & " Learners").Index, e.RowIndex).ReadOnly = True
            VolumesDataGrid.Item(VolumesDataGrid.Columns(YearLabel & " Achieve").Index, e.RowIndex).ReadOnly = True
        Else
            If VolumesDataGrid.Item(VolumesDataGrid.Columns("Locked").Index, e.RowIndex).Value = 1 Then
                VolumesDataGrid.Item(VolumesDataGrid.Columns(YearLabel & " Learners").Index, e.RowIndex).ReadOnly = True
                'VolumesDataGrid.Item(VolumesDataGrid.Columns("2010/11 Achieve").Index, e.RowIndex).ReadOnly = True

                If VolumesDataGrid.Item(VolumesDataGrid.Columns("Is_Header_Line").Index, e.RowIndex).Value = 1 And VolumesDataGrid.Item(VolumesDataGrid.Columns("Is_Total_Line").Index, e.RowIndex).Value = 1 Then
                Else
                    If VolumesDataGrid.Columns(YearLabel & " Learners").Index = e.ColumnIndex Then
                        e.CellStyle.BackColor = Color.Linen
                    End If

                    If VolumesDataGrid.Columns(YearLabel & " Achieve").Index = e.ColumnIndex Then
                        e.CellStyle.BackColor = Color.Linen
                    End If
                End If

            Else
                VolumesDataGrid.Item(VolumesDataGrid.Columns(YearLabel & " Learners").Index, e.RowIndex).ReadOnly = False
                VolumesDataGrid.Item(VolumesDataGrid.Columns(YearLabel & " Achieve").Index, e.RowIndex).ReadOnly = False
            End If
        End If

ErrorCatch:
        'Dont do nuffink.  The Period label is not ready yet.
        Resume Next

    End Sub

    Private Sub LoadCurrentLABox()
        Dim sql As String

        Job("Loading Local Authorities...")

        CanLABoxIndexChangeRun = False
        sql = "Select LA.LA_Code, LA.LA_Name " & _
              "from dbo.Relationships R,dbo.Local_Authorities LA " & _
              "where LA.LA_Code = R.LA_Code " & _
              "and R.UPIN = " & UPIN

        CurrentLABox.DataSource = LoadDataView(sql)
        CurrentLABox.DisplayMember = "LA_Name"
        CurrentLABox.ValueMember = "LA_Code"

        CurrentLABox.SelectedValue = CurrentLACode
        CanLABoxIndexChangeRun = True

    End Sub

    Private Sub LoadInfoGrid()
        Job("Loading Provider Information...")

        Dim Sql As String
        Dim ProviderType As String
        Dim TheData As DataView

        Sql = "select * from Providers where UPIN = " & UPIN
        TheData = LoadDataView(Sql)

        ProviderType = TheData(0).Item("Provider_Type_Code").ToString

        Sql = "select i.*, d.Information as Input_Field from Information_Fields i " & _
              "left outer join (Select * from Information_Data where RID = '" & RID & "') d on (i.Field_Code = d.Field_Code)" & _
              " where Provider_Type_Code = '" & ProviderType & "' order by Order_Field"

        InfoGrid.DataSource = LoadDataView(Sql)

        'hide columns that need to be hidden

        InfoGrid.Columns("Provider_Type_Code").Visible = False
        InfoGrid.Columns("Field_Code").Visible = False
        InfoGrid.Columns("Order_Field").Visible = False
        InfoGrid.Columns("Formatting").Visible = False
        InfoGrid.Columns("Field_Type").Visible = False
        InfoGrid.Columns("Lookup_Table").Visible = False
        InfoGrid.Columns("Lookup_Value").Visible = False
        InfoGrid.Columns("Lookup_Field").Visible = False

        InfoGrid.Columns("Field_Description").ReadOnly = True

        Dim colDesc As DataGridViewColumn = InfoGrid.Columns("Input_Field")

        colDesc.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

        colDesc.DefaultCellStyle.WrapMode = DataGridViewTriState.True

        InfoGrid.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCells

        InfoChangesMade = False


    End Sub

    Private Sub LoadVolumesDataGrid()
        Job("Loading Volumes Data...")


        Dim sql As String
        Dim IsLive As Boolean

        Dim TheView As DataView

        sql = "Select * from Periods where Period_ID = " & PeriodBox.SelectedValue

        TheView = LoadDataView(sql)

        YearLabel = TheView(0).Item("Period_Name").ToString
        TheView = Nothing


        If VersionBox.SelectedValue.ToString = "Live" Then

            IsLive = True

            sql = "select FLP.Field_Code, Order_Field, Field_Description as ' ', Category_Code, " & _
                    "Has_Achievement, Is_Total_Line, Is_Header_Line, Is_Auto_Total_Line, "

            'this needs to be sorted for next year
            If PeriodCode = 2010111 Then
                sql = sql & "Act89.Learners as '2008/09 F05 Learners', Act910.Learners as '2009/10 F01 Learners', "
            End If



            sql = sql & "VD.Learners as '" & YearLabel & " Learners', Achievements as '" & YearLabel & " Achieve', 0 as Locked " & _
                "from Fields_List_Part as FLP " & _
                "left outer join " & _
                "(Select * from dbo.Volumes_Data " & _
                "where RID = '" & RID & "' and Period_ID = " & PeriodCode & ") VD " & _
                "on FLP.Field_Code = VD.Field_Code "



            If PeriodCode = 2010111 Then
                sql = sql & "left outer JOIN " & _
                    "( SELECT * " & _
                    "FROM [dbo].[YP_200809F05_Act] " & _
                    "where RID = '" & RID & "') Act89 " & _
                    "on FLP.Field_Code = Act89.Field_Code " & _
                    "left outer JOIN " & _
                    "( SELECT * " & _
                    "FROM [dbo].[YP_200910F01_Act] " & _
                    "where RID = '" & RID & "') Act910 " & _
                    "on FLP.Field_Code = Act910.Field_Code "
            Else
                sql = sql & " where FLP.Field_Code in (1000,1013)"
            End If

            sql = sql & "order by order_field"

        Else

            IsLive = False

            sql = "select FLP.Field_Code, Order_Field, Field_Description as ' ', Category_Code, " & _
                    "Has_Achievement, Is_Total_Line, Is_Header_Line, Is_Auto_Total_Line, "


            'this needs to be sorted for next year
            If PeriodCode = 2010111 Then
                sql = sql & "Act89.Learners as '2008/09 F05 Learners', Act910.Learners as '2009/10 F01 Learners', "
            End If


            sql = sql & "VD.Learners as '" & YearLabel & " Learners', Achievements as '" & YearLabel & " Achieve', 0 as Locked " & _
                    "from Fields_List_Part as FLP " & _
                    "left outer join " & _
                    "(Select * from dbo.Volumes_Data_backup " & _
                    "where RID = '" & RID & "' and Period_ID = " & PeriodCode & " and Version = " & VersionBox.SelectedValue.ToString & ") VD " & _
                    "on FLP.Field_Code = VD.Field_Code "


            If PeriodCode = 2010111 Then
                sql = sql & "left outer JOIN " & _
                    "( SELECT * " & _
                    "FROM [dbo].[YP_200809F05_Act] " & _
                    "where RID = '" & RID & "') Act89 " & _
                    "on FLP.Field_Code = Act89.Field_Code " & _
                    "left outer JOIN " & _
                    "( SELECT * " & _
                    "FROM [dbo].[YP_200910F01_Act] " & _
                    "where RID = '" & RID & "') Act910 " & _
                    "on FLP.Field_Code = Act910.Field_Code "
            Else
                sql = sql & " where FLP.Field_Code in (1000,1013)"
            End If

            sql = sql & "order by order_field"


        End If

        VolumesDataGrid.DataSource = LoadDataView(sql)
        VolumesDataGrid.Refresh()

        'Stop user from reordering the grid
        Dim i As Integer

        For i = 0 To VolumesDataGrid.Columns.Count - 1

            VolumesDataGrid.Columns.Item(i).SortMode = DataGridViewColumnSortMode.NotSortable

        Next

        ChangesMade = False

        If IsLive Then
            Locking()
        End If

        VolumesDataGrid.Columns(0).Frozen = True
        VolumesDataGrid.Columns(1).Frozen = True
        VolumesDataGrid.Columns(2).Frozen = True

        If PeriodCode <> 2010111 Then
            VolumesDataGrid.Columns(9).Visible = False
        Else
            VolumesDataGrid.Columns(9).Visible = True

        End If



    End Sub

    Public Sub CurrentProviderName(ByVal ProviderName As String, ByVal TheRID As String, ByVal LACode As String, ByVal theUPIN As Integer)

        RID = TheRID
        UPIN = theUPIN
        CurrentProviderLabel.Text = UPIN.ToString & " - " & ProviderName
        Me.Text = UPIN.ToString & " - " & ProviderName


        CurrentLACode = LACode

        LoadURNandEstab(UPIN)

    End Sub


    Private Sub VolumesDataGrid_DataError(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles VolumesDataGrid.DataError
        MessageBox.Show(Me, "You can only enter numbers, please change your entry to a number", "Numbers only", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Sub

    Private Sub BackupData()
        Dim Version As Integer
        Dim TheData As DataView
        Dim Sql As String

        'Is there anything to backup
        Sql = "select count(*) as TheCount from Volumes_Data where RID = '" & RID & "' and Period_ID = '" & PeriodCode & "'"
        TheData = LoadDataView(Sql)

        If TheData(0).Item("TheCount") = 0 Then
            TheData = Nothing
            Exit Sub
        End If

        'Get the Version Number
        Sql = "select isnull(max(version),0) as Version from volumes_data_backup where RID = '" & RID & "' and Period_ID = '" & PeriodCode & "'"
        TheData = LoadDataView(Sql)

        Version = TheData(0).Item("Version") + 1

        TheData = Nothing

        Sql = "insert into Volumes_data_backup select " & Version & " as version, * from Volumes_Data where RID = '" & RID & "' and Period_ID = '" & PeriodCode & "'"

        RunNonQuery(Sql)

        AddActivity(CurrentUser.Name, "Volumes data were backed up for RID " & RID & " and Period " & PeriodCode & " as Version " & Version, 2, Version)
    End Sub

    Private Sub SaveInfoData()
        Dim sql As String
        Dim r As Integer

        Job("Saving Data...")
        'first delete all the records
        sql = "delete from Information_Data where RID = '" & RID & "'"

        RunNonQuery(sql)

        For r = 0 To InfoGrid.RowCount - 1
            If InfoGrid.Item(InfoGrid.Columns("Input_Field").Index, r).Value.ToString.Length <> 0 Then
                sql = "insert into Information_Data (RID, Field_Code, Information) " & _
                  "values ('" & RID & "', " & InfoGrid.Item(InfoGrid.Columns("Field_Code").Index, r).Value & ", '" & InfoGrid.Item(InfoGrid.Columns("Input_Field").Index, r).Value & "')"
                RunNonQuery(sql)
            End If

        Next
        MessageBox.Show("Information was saved", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information)
        InfoChangesMade = False
    End Sub

    Private Sub SaveData()
        Dim r As Integer
        Dim FC As Integer
        Dim Learners As Integer
        Dim Achievements As Integer
        Dim ErrorCount As Integer
        Dim RowsAdded As Integer


        Job("Saving Data...")

        Me.Refresh()
        If ChangesMade = False Then
            MessageBox.Show(Me, "No changes were made to save", "No changes made", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If




        ErrorCount = 0
        'First backup the old version before saving the new version
        BackupData()


        'First delete data
        If RunNonQuery("delete from Volumes_Data where RID = '" & RID & "' and Period_ID = " & PeriodCode) <> "" Then
            MessageBox.Show(Me, "There was an errer when trying to save the data", "Delete Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            'Run down grid to insert data
            RowsAdded = 0
            For r = 0 To VolumesDataGrid.RowCount - 1
                If VolumesDataGrid.Item(VolumesDataGrid.Columns(YearLabel & " Learners").Index, r).Value.ToString.Length <> 0 Then
                    Learners = VolumesDataGrid.Item(VolumesDataGrid.Columns(YearLabel & " Learners").Index, r).Value
                Else
                    Learners = 0
                End If

                If VolumesDataGrid.Item(VolumesDataGrid.Columns(YearLabel & " Achieve").Index, r).Value.ToString.Length <> 0 Then
                    Achievements = VolumesDataGrid.Item(VolumesDataGrid.Columns(YearLabel & " Achieve").Index, r).Value
                Else
                    Achievements = 0
                End If

                FC = VolumesDataGrid.Item(VolumesDataGrid.Columns("Field_Code").Index, r).Value

                'Dont add blank or zero rows
                If Learners + Achievements <> 0 Then
                    RowsAdded = RowsAdded + 1
                    If RunNonQuery("insert into Volumes_Data Values('" & RID & "', " & _
                                FC & ", " & PeriodCode & ", " & Learners & ", " & Achievements & ")") <> "" Then
                        ErrorCount = ErrorCount + 1
                    End If
                End If
            Next
        End If

        If RowsAdded > 0 Then
            AddActivity(CurrentUser.Name, "New volumes data were saved for RID " & RID & " and Period " & PeriodCode, 3, "")
        End If



        If ErrorCount > 0 Then
            MessageBox.Show(Me, "There was an error when trying to save the data", "Insert Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            ChangesMade = False

            MessageBox.Show(Me, "The data was succesfully saved", "Data Saved", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub


    Private Sub SaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveButton.Click
        SaveData()
        LoadVersionBox()

    End Sub

    Private Sub VolumesDataGrid_CellValueChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles VolumesDataGrid.CellValueChanged
        ChangesMade = True
    End Sub

    Private Sub VolumesForm_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
        If VersionBox.SelectedValue.ToString <> "Live" Then Exit Sub

        If CurrentUser.Level <> 0 Then
            If ChangesMade = True Then
                If MessageBox.Show(Me, "Do you want to save your changes before exiting?  (If you select No, all the changes you have made will be lost)", "Save Changes?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                    SaveData()
                End If
            End If

        End If

        If InfoChangesMade = True Then
            If MessageBox.Show(Me, "Do you want to save your changes you made to the information tab before exiting?  (If you select No, all the changes you have made will be lost)", "Save Changes?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                SaveInfoData()
            End If
        End If

    End Sub

    Private Sub ExportButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExportButton.Click
        Dim result As Boolean


        SaveFileDialog.FileName = CurrentProviderLabel.Text & ", " & DirectCast(CurrentLABox.SelectedItem, DataRowView).Item("LA_Name") & ".xls"

        If SaveFileDialog.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Job("Exporting Volumes Data...")

            Me.Cursor = Cursors.WaitCursor
            Me.Refresh()



            result = ExportVolumesToExcel(SaveFileDialog.FileName, VolumesDataGrid, CurrentProviderLabel.Text, _
                                          DirectCast(CurrentLABox.SelectedItem, DataRowView).Item("LA_Name"), RID, YearLabel)


            Me.Cursor = Cursors.Arrow

            If result = False Then
                MessageBox.Show(Me, "There was an error exporting the data.", "Error exporting", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                MessageBox.Show(Me, "The data was exported successfully", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        End If
        Me.Focus()
    End Sub

    Private Sub ImportButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ImportButton.Click
        Dim result As Boolean

        If OpenFileDialog.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Me.Cursor = Cursors.WaitCursor
            Me.Refresh()

            Job("Importing Volumes Data...")

            result = ImportVolumesFromExcel(OpenFileDialog.FileName, VolumesDataGrid, RID, YearLabel)


            Me.Cursor = Cursors.Arrow

            If result = False Then
                MessageBox.Show(Me, "There was an error importing the data.  Not all data was imported.  Make sure the file you are trying to import is not open in Excel.", "Error importing", MessageBoxButtons.OK, MessageBoxIcon.Error)

            Else
                MessageBox.Show(Me, "The data was imported successfully", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)

            End If
        End If

        Me.Focus()

    End Sub

    Private Sub CurrentLABox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CurrentLABox.SelectedIndexChanged

        If CanLABoxIndexChangeRun = True Then
            If CurrentLACode <> DirectCast(CurrentLABox.SelectedItem, DataRowView).Item("LA_Code") Then
                If ChangesMade = True Then
                    If MessageBox.Show(Me, "Do you want to save your changes before changing the Local Authority for this provider?  (If you select No, all the changes you have made will be lost)", "Save Changes?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                        SaveData()
                    End If
                End If
                CurrentLACode = DirectCast(CurrentLABox.SelectedItem, DataRowView).Item("LA_Code")
                LoadNewRID(UPIN, CurrentLACode)
                LoadVersionBox()
                SetButtons()
                LoadVolumesDataGrid()
                LoadFundingGrid()
            End If
        End If
    End Sub


    Private Sub ReportButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ReportButton.Click
        Dim sql As String

        Job("Loading Volumes Report...")

        sql = "select FLP.Field_Code, Order_Field, Field_Description, Category_Code, " & _
                    "Has_Achievement, Is_Total_Line, Is_Header_Line, Is_Auto_Total_Line, " & _
                    "Act89.Learners as '2008/09 F05 Learners', Act910.Learners as '2009/10 F01 Learners', " & _
                    "VD.Learners as '2010/11 Learners', Achievements as '2010/11 Achievements' " & _
                    "from Fields_List_Part as FLP " & _
                    "left outer join " & _
                    "(Select * from dbo.Volumes_Data " & _
                    "where RID = '" & RID & "' and Period_ID = " & PeriodCode & ") VD " & _
                    "on FLP.Field_Code = VD.Field_Code " & _
                    "left outer JOIN " & _
                    "( SELECT * " & _
                    "FROM [dbo].[YP_200809F05_Act] " & _
                    "where RID = '" & RID & "') Act89 " & _
                    "on FLP.Field_Code = Act89.Field_Code " & _
                    "left outer JOIN " & _
                    "( SELECT * " & _
                    "FROM [dbo].[YP_200910F01_Act] " & _
                    "where RID = '" & RID & "') Act910 " & _
                    "on FLP.Field_Code = Act910.Field_Code " & _
                    "order by order_field"
        ShowReport("Young People Learning Volumes", "VolumesReport.rdl", "TheStore", sql, "", "", "", "", "", "", "", "", _
                   "ProviderName", CurrentProviderLabel.Text, "LocalAuthority", DirectCast(CurrentLABox.SelectedItem, DataRowView).Item("LA_Name"), "", "", "", "", "", "", "", "", "", "", "", "", False, False)

        '  LoadReport("Young People Learning Volumes", "VolumesReport.rpt", sql, "", "", "", "", _
        '           "ProviderName", CurrentProviderLabel.Text, "LocalAuthority", DirectCast(CurrentLABox.SelectedItem, DataRowView).Item("LA_Name"), "", "")


    End Sub



    Private Sub VersionBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles VersionBox.SelectedIndexChanged
        If CanVersionBoxIndexChangeRun = False Then Exit Sub

        If ChangesMade = True Then
            If VersionBox.SelectedValue = "Live" Then
                Exit Sub
            End If

            If MessageBox.Show("All changes made will be lost, are you sure you want to continue?", "Continue?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
                VersionBox.SelectedValue = "Live"
                Exit Sub
            End If
        End If

        SetButtons()
        LoadVolumesDataGrid()

    End Sub

    Private Sub LiveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LiveButton.Click
        If MessageBox.Show(Me, "Are you sure you want to make this the Live version?", "Change this version to Live?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            ChangesMade = True
            SaveData()
            LoadVersionBox()
            LoadVolumesDataGrid()
            SetButtons()
        End If
    End Sub


    Private Sub FundingGrid_CellFormatting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles FundingGrid.CellFormatting
        e.CellStyle.Font = New Font("Arial", 8.25, FontStyle.Regular)

        If FundingGrid.Item(FundingGrid.Columns("Type").Index, e.RowIndex).Value.ToString = "1" Then
            e.CellStyle.BackColor = Color.LightGray
            e.CellStyle.Font = New Font("Arial", 8.25, FontStyle.Bold)
        End If

        If FundingGrid.Item(FundingGrid.Columns("Type").Index, e.RowIndex).Value.ToString = "0" Then
            e.CellStyle.BackColor = Color.LightBlue
            e.CellStyle.Font = New Font("Arial", 8.25, FontStyle.Bold)
        End If

        If e.ColumnIndex >= 4 Then
            e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            If FundingGrid.Columns(e.ColumnIndex).Name = "Allocation" Then


                FundingGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "£#,#"


            End If

            If FundingGrid.Columns(e.ColumnIndex).Name = "SLN" Then
                FundingGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "#,#.##"
            End If

            If FundingGrid.Columns(e.ColumnIndex).Name = "Rate" Then
                FundingGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "#,#"
            End If

            If FundingGrid.Columns(e.ColumnIndex).Name = "Provider Factor" Then
                FundingGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "#,#.###"
            End If

            If FundingGrid.Columns(e.ColumnIndex).Name = "Learners" Then
                FundingGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "#,#"
            End If

            If FundingGrid.Columns(e.ColumnIndex).Name = "SLN Ratio" Then
                FundingGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "#,#.##"
            End If
        End If
    End Sub

    Private Sub FundingGrid_CellEnter(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles FundingGrid.CellEnter
        If FundingGrid.RowCount = 0 Then Exit Sub

        HistoryLabel.Text = "History for " & FundingGrid.Item(2, FundingGrid.CurrentRow.Index).Value.ToString

        SetButtons()
        LoadHistoryGrid()


    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChangeReqBut.Click
        If FundingGrid.SelectedRows.Count = 0 Then Exit Sub

        MessageBox.Show("Warning!!! It is not recommended to make changes to the allocations without updating the model.  Please be aware that any changes made will be overwritten when the model is applied", "Warning!", MessageBoxButtons.OK, MessageBoxIcon.Warning)


        With ChangeRequestDialog
            .ProviderLabel.Text = CurrentProviderLabel.Text
            .LALabel.Text = DirectCast(CurrentLABox.SelectedItem, DataRowView).Item("LA_Name")
            .AllocationLabel.Text = FundingGrid.Item(FundingGrid.Columns("Description").Index, FundingGrid.CurrentRow.Index).Value.ToString
            .AllocationBox.Text = Decimal.Parse(FundingGrid.Item(FundingGrid.Columns("Allocation").Index, FundingGrid.CurrentRow.Index).Value).ToString("c")
            .RateBox.Text = Decimal.Parse(FundingGrid.Item(FundingGrid.Columns("Rate").Index, FundingGrid.CurrentRow.Index).Value).ToString("c")
            .PFBox.Text = FundingGrid.Item(FundingGrid.Columns("Provider Factor").Index, FundingGrid.CurrentRow.Index).Value.ToString
            .LearnerBox.Text = FundingGrid.Item(FundingGrid.Columns("Learners").Index, FundingGrid.CurrentRow.Index).Value.ToString

            .NotesBox.Text = ""
        End With

        If ChangeRequestDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
            'change the allocation
            Dim AllocationLine As String
            Dim Allocation As Decimal
            Dim Rate As Decimal
            Dim PF As Decimal
            Dim Learners As Integer
            Dim notes As String


            AllocationLine = FundingGrid.Item(FundingGrid.Columns("ID").Index, FundingGrid.CurrentRow.Index).Value.ToString
            Allocation = ChangeRequestDialog.AllocationBox.Text
            Rate = ChangeRequestDialog.RateBox.Text
            PF = ChangeRequestDialog.PFBox.Text
            Learners = ChangeRequestDialog.LearnerBox.Text
            notes = ChangeRequestDialog.NotesBox.Text

            'before you do anyting update the pattern
            ' If VaryPaymentPatternV2(RID, AllocationLine, PeriodCode, Allocation) = False Then
            ' MessageBox.Show("Changes were cancelled", "Cancelled", MessageBoxButtons.OK, MessageBoxIcon.Information)
            ' Exit Sub
            ' End If

            Dim sql As String

            'first change the approved allocation to history
            sql = "Update Allocations set Status = 'History' " & _
                " where Period_ID = " & PeriodCode & _
                " and RID = '" & RID & "' " & _
                " and Allocation_ID = '" & AllocationLine & "' " & _
                " and Status = 'Approved'"

            RunNonQuery(sql)

            'now insert the allocation record
            sql = "insert into Allocations " & _
                " ([Period_ID],[RID],[Allocation_ID],[TheDate],[User_Name],[Allocation],[Rate],[Provider_Factor],[Learner_Numbers],[Status],[Notes]) " & _
                " values (" & PeriodCode & ", '" & RID & "', '" & AllocationLine & "', " & Now.ToOADate - 2 & ", " & _
                " '" & CurrentUser.Name & "', " & Allocation & ", " & Rate & ", " & PF & ", " & Learners & ", 'Approved', '" & notes & "')"


            RunNonQuery(sql)
            LoadFundingGrid()
            AddActivity(CurrentUser.Name, "User made allocation changes for " & CurrentProviderLabel.Text, 20, "")

        End If
    End Sub


    Private Sub HistoryGrid_CellFormatting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles HistoryGrid.CellFormatting
        If e.ColumnIndex >= 4 Then
            e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            If HistoryGrid.Columns(e.ColumnIndex).Name = "Allocation" Then
                HistoryGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "£#,#"
            End If

            If HistoryGrid.Columns(e.ColumnIndex).Name = "SLN" Then
                HistoryGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "#,#.##"
            End If

            If HistoryGrid.Columns(e.ColumnIndex).Name = "Rate" Then
                HistoryGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "#,#"
            End If

            If HistoryGrid.Columns(e.ColumnIndex).Name = "Provider Factor" Then
                HistoryGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "#,#.###"
            End If

            If HistoryGrid.Columns(e.ColumnIndex).Name = "Learners" Then
                HistoryGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "#,#"
            End If

            If HistoryGrid.Columns(e.ColumnIndex).Name = "SLN Ratio" Then
                HistoryGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "#,#.##"
            End If
        End If
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sql, StartDate, EndDate As String
        Dim TheDataview As DataView
        ' Dim DLFLearners, SchoolLearners, LLDDLearners As Integer
        Job("Loading Payment Schedule...")


        sql = "select r.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month, " & _
              "Sum(PP.Month_Total) as Month_Total, 0 as FYMonth, sum(PP.Learner_Numbers) as Learners " & _
              "from PaymentProfilesV2 PP, " & _
              "Relationships R, " & _
              "Providers P, " & _
              "Budgets B, " & _
              "Allocation_Lines A " & _
              "where R.RID = PP.RID " & _
              "and P.UPIN = R.UPIN " & _
              "and A.Allocation_ID = PP.Allocation_ID " & _
              "and B.Budget_ID = A.Budget_ID  " & _
              "and pp.RID = '" & RID & "' " & _
              "and pp.Period_ID = " & PeriodCode & _
              " group by " & _
              "r.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month"

        '    sql = "select r.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month, " & _
        '          "Sum(PP.Month_Total) as Month_Total, 0 as FYMonth " & _
        '          "from PaymentProfiles PP, " & _
        '          "Relationships R, " & _
        '          "Providers P, " & _
        '          "Budgets B " & _
        '          "where R.RID = PP.RID " & _
        '          "and P.UPIN = R.UPIN " & _
        '          "and B.Budget_ID = PP.Budget_ID  " & _
        '          "and pp.RID = '" & RID & "' " & _
        '          "and pp.Period_ID = " & PeriodCode & _
        '          "group by " & _
        '          "r.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month"

        ' TheDataview = LoadDataView("select max(TheDate) as TheDate from allocations where RID = '" & RID & "' and Status = 'Approved' and Period_ID = " & PeriodCode)
        'TheDate = FormatDateTime(TheDataview(0).Item("TheDate"), DateFormat.LongDate)

        '      TheDataview = LoadDataView("select coalesce(sum(School_Learners),0) as School_Learners, " & _
        '                                   "coalesce(sum(DLF_Learners),0) as DLF_Learners, " & _
        '                                  "coalesce(sum(LLDD_Learners),0) as LLDD_Learners " & _
        '                                "from dbo.LearnerSplit pp, Relationships R " & _
        '                              "where R.RID = pp.RID " & _
        '                            "and pp.Period_ID = " & PeriodBox.SelectedValue.ToString & " " & _
        '                          "and R.RID = '" & RID & "'")

        '     If TheDataview.Count <> 0 Then
        'DLFLearners = TheDataview(0).Item("DLF_Learners")
        '    SchoolLearners = TheDataview(0).Item("School_Learners")
        '    LLDDLearners = TheDataview(0).Item("LLDD_Learners")
        '    Else
        '    DLFLearners = 0
        '    SchoolLearners = 0
        '    LLDDLearners = 0
        '    End If

        TheDataview = LoadDataView("select * from Periods where Period_ID = " & PeriodCode)
        StartDate = FormatDateTime(TheDataview(0).Item("Start_Date"), DateFormat.LongDate)
        EndDate = FormatDateTime(TheDataview(0).Item("End_Date"), DateFormat.LongDate)



        ShowReport("Payment Schedule for " & CurrentProviderLabel.Text, "PaymentSchedule.rdl", "TheStore", sql, "", "", "", "", _
                   "LAHeader", DirectCast(CurrentLABox.SelectedItem, DataRowView).Item("LA_Name"), _
                   "PeriodString", "Allocation Start Date: " & StartDate & "          Allocation End Date: " & EndDate, _
                   "ProviderHeader", CurrentProviderLabel.Text & " (UPIN " & UPIN.ToString & ")", _
                   "", "", "", "", "", "", "", "", "", "", "", "", "", "", False, False)
        Job("Ready")
    End Sub

    Private Sub InfoGrid_CellFormatting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles InfoGrid.CellFormatting
        'format the cells

        If InfoGrid.Item(InfoGrid.Columns("Formatting").Index, e.RowIndex).Value = 2 Then
            e.CellStyle.BackColor = Color.FromArgb(150, 0, 85)
            e.CellStyle.Font = New Font("Arial", 8.25, FontStyle.Bold)
            e.CellStyle.ForeColor = Color.White
            InfoGrid.Item(InfoGrid.Columns("Formatting").Index, e.RowIndex).ReadOnly = True
        End If

        If InfoGrid.Item(InfoGrid.Columns("Formatting").Index, e.RowIndex).Value = 1 Then
            e.CellStyle.BackColor = Color.FromArgb(248, 227, 162)
            e.CellStyle.Font = New Font("Arial", 8.25, FontStyle.Bold)
        End If

    End Sub


    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        SaveInfoData()
    End Sub

    Private Sub InfoGrid_CellValueChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles InfoGrid.CellValueChanged
        InfoChangesMade = True

    End Sub

    Private Sub Button2_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim sql As String

        sql = "select * from (select f.Field_Code as Real_Field_Code, f.Field_Description as Real_Description, f.Formatting as Real_Formatting, f.Order_Field as Real_Order, i.* " & _
              "from dbo.Information_Fields f left outer join " & _
              "(select * from dbo.Information_Data_Report where RID = '" & RID & "') i on (i.Field_Code = f.Field_Code)) a order by Order_Field"

        ShowReport("Information", "Information_Data.rdl", "TheStore", sql, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", False, False)

    End Sub

    Private Sub PeriodBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PeriodBox.SelectedIndexChanged
        Try
            PeriodCode = PeriodBox.SelectedValue
        Catch ex As Exception
            Exit Sub

            'the periodbox is not ready yet
        End Try
        LoadVersionBox()
        LoadVolumesDataGrid()
        LoadCurrentLABox()
        LoadFundingGrid()
    End Sub
End Class